import logging
from typing import Union, List, Dict, Tuple

from pydantic import BaseModel

from common_libs.clutter_utils import string_format

# from dispatcher.common_utils.constants import logger

logger = logging.getLogger()

def _in(field, value):
    return f"{field} IN ({string_format(value, separate=',')})"

def _not_in(field, value):
    return f"{field} IN ({string_format(value, separate=',')})"

def _gt(field, value):
    return f"{field} > {value}"

def _lt(field, value):
    return f"{field} < {value}"

def _e(field, value):
    return f"{field} = {value}"

def _gte(field, value):
    return f"{field} >= {value}"

def _lte(field, value):
    return f"{field} <= {value}"

def _ne(field, value):
    return f"{field} != {value}"

def _ll(field, value):
    return f"{field} LIKE %%{value}"

def _rl(field, value):
    return f"{field} LIKE {value}%%"

def _al(field, value):
    return f"{field} LIKE %%{value}%%"

def _is_null(field, value):
    return f"{field} IS NULL" if value else f"{field} IS NOT NULL"

def _or(field, value):
    return f"{string_format(value, separate=' OR ', boundary=True)}"

def __group_by(field, value):
    return f"GROUP BY {string_format(value, separate=',')}"

def __order_by(field, value):
    value = [node.replace(" = ", " ") if not isinstance(node, tuple) else f"{node[0]} {node[1]}" for node in value]
    return f"ORDER BY {string_format(value, separate=',')}"

def __limit(field, value):
    value = value if isinstance(value, list) else [value]
    return f"LIMIT {string_format(value, separate=',')}"

def __offset(field, value):
    return f"OFFSET {value}"

def __end(field, value):
    if isinstance(value, list):
        return list(map(lambda node: f"{str(node[0])}.{str(node[1])}", [(field, string) for string in value]))
    return _e(field, value)

condition_map = {
    "in": _in,
    "not_in": _not_in,
    "gt": _gt,
    "lt": _lt,
    # "e": _e,
    "gte": _gte,
    "lte": _lte,
    "ne": _ne,
    "ll": _ll,
    "rl": _rl,
    "al": _al,
    "is_null": _is_null,
    "or": _or,
    "group_by": __group_by,
    "order_by": __order_by,
    "limit": __limit,
    "offset": __offset,
    "end": __end
}

def meta_condition_map(field_cond, value):
    if value is None or value == "" or value == []:
        return
    field, cond, *_ = f"{field_cond}__end".split("__", maxsplit=2)
    return condition_map[cond](field, value)

def flat_condition_tree(node: Dict):
    """
    数据表连表, 条件拼接操作解析, 支持大部分常用操作
        如: ["in", ">", ">=", "<", "<=", "=", "group by", "order_by", "offset", "limit", "or"]

    >>> temp = {
            "user.name": 23,
            "name": "tom",

            "user.id__in": [1,2,3],
            "user": {"id__in": [1, 2, 3], "age__gt": 100},

            "is_deleted__is_null": False,

            "__limit": 20,
            "__limit": [1, 100],

            "__offset": 10,
            "__or": {
                "user": {"id": 10},
                "city": {"id": 20},
                "test.id__gt": 23,
                "test.datetime": "2022"
            },
            "__group_by": {
                "user": ["id", "name"],
                "city": ["name"]
            },
            "__order_by": {
                "user.grade": "ASC",
                "user": {"id": "ASC", "age": "DESC"},
                "city": {"id": "ASC"}
            },
            "__order_by": [("id", "CSA"), "age"],
    }
    >>> flat_condition_tree(temp)
    return
    ['user.name = 23', 'name = tom', 'user.id IN (1,2,3)', 'user.id IN (1,2,3)', 'user.age > 100',
    'is_deleted IS NOT NULL', 'LIMIT 1,100', 'OFFSET 10',
    '(user.id = 10 OR city.id = 20 OR test.id > 23 OR test.datetime = 2022)',
    'GROUP BY user.id,user.name,city.name', 'ORDER BY user.grade ASC,user.id ASC,user.age DESC,city.id ASC']

    :return: int
    """
    strings = []
    for field_cond, value in node.items():
        if isinstance(value, Dict):
            value = flat_condition_tree(node=value)
        result = meta_condition_map(field_cond=field_cond, value=value)
        strings.extend(result if isinstance(result, list) else [result])

    return strings



class FieldsAliasListModel(BaseModel):
    # todo 应该优化为 model
    _table: str = None
    _tb_fields: Union[Tuple, str] = None

def db_field_analysis(db_fields: Union[List[str], Dict[str, List[Union[Tuple[str, str], str]]]]):
    """
    数据表连表，取字段操作入参解析,

    List: 传递一个 fields 列表，则说明不是连表操作，或者虽然是连表操作，但是取值字段是唯一值
        >>> field1 = ["a", "b", "cc"]
        >>> db_field_analysis(field1)
        return

    Dict: 连表操作，指定获取某个表的字典，同时支持取别名, {table_name: [(table.fields_name, alias), ...]}
        >>> field2 = {"user": [("aa", "A"), "bb"]}
        >>> db_field_analysis(field2)
        return

    :return: str
    """
    # todo 这里的各个字段应该和数据表模型绑定，只能是规定的字符，避免SQL注入
    try:
        if isinstance(db_fields, list):
            return string_format(strs=db_fields, separate=",", boundary=False)

        strings = []
        for table, tb_fields in db_fields.items():
            assert isinstance(tb_fields, list)
            for field in tb_fields:
                field_and_alias = f"{field[0]} AS {field[1]}" if isinstance(field, tuple) else str(field)
                strings.append(f"{table}.{field_and_alias}")
        return string_format(strs=strings, separate=",", boundary=False)
    except AssertionError:
        try:
            # 这一部分用于处理因代码问题而导致的解析错误
            assert FieldsAliasListModel.parse_raw(db_fields)
        except Exception as e:
            raise TypeError(f"解析错误，error: {e}, db_fields: {db_fields}, 解析格式为：Dict[str, List[Union[Tuple[str, str], str]]]]")
        logger.info(f"格式错误， db_fields: {db_fields}, 正确格式：Dict[str, List[Union[Tuple[str, str], str]]]]")

def db_condition_analysis(condition: Dict):
    """
    数据表连表, 条件拼接操作解析, 支持大部分常用操作
        如: ["in", ">", ">=", "<", "<=", "=", "group by", "order_by", "offset", "limit", "or"]

    :param: condition Dict
    :return: str
    """
    # 讲后缀操作与普通条件操作分开
    suffix_condition_flag = ["__order_by", "__group_by", "__limit", "__offset"]
    suffix_condition = {cond:condition[cond] for cond in condition.keys() if cond in suffix_condition_flag}
    common_condition = {cond:condition[cond] for cond in condition.keys() if cond not in suffix_condition_flag}

    common_condition_str = string_format(flat_condition_tree(common_condition), separate=" AND ")
    suffix_condition_str = string_format(flat_condition_tree(suffix_condition), separate=" ")
    return  string_format([common_condition_str, suffix_condition_str], separate=" ")


if __name__ == '__main__':
    # import doctest
    # doctest.testmod()
    temp = {
        # "user.name": 23,
        # "name": "tom",
        #
        # "user.id__in": [1, 2, 3],
        # "user": {"id__in": [1, 2, 3], "age__gt": 100},
        #
        # "is_deleted__is_null": False,
        #
        # # "__limit": 20,
        # "__limit": [1, 100],
        #
        # "__offset": 10,
        # "__or": {
        #     "user": {"id": 10},
        #     "city": {"id": 20},
        #     "test.id__gt": 23,
        #     "test.datetime": "2022"
        # },
        # "__group_by": {
        #     "user": ["id", "name"],
        #     "city": ["name"]
        # },
        "__order_by": {
            "user.grade": "ASC",
            # "user": {"id": "ASC", "age": "DESC"},
            # "city": {"id": "ASC"},
            "user": [("id", True), ("age", False)],
            "city": ("id", True)
        },
        # "__order_by": [("id", "CSA"), "age"]
    }
    print(flat_condition_tree(temp))
